﻿package util;

import java.sql.*;

public class DataBaseCreate {
	public static void create(){
		Connection conn = null;
		try {
			
			// 连接SQLite的JDBC
			Class.forName("org.sqlite.JDBC");
			
			// 建立一个数据库名test.db的连接，如果不存在就在当前目录下创建之
			conn = DriverManager
					.getConnection("jdbc:sqlite:test.db");
			
			Statement stat = conn.createStatement();
			try{
				//stat.executeUpdate("drop table if exists user;");
				// 创建表user 用户表
				stat.executeUpdate("CREATE TABLE user ("+
				"id Integer primary key autoincrement,"
				+"username TEXT NOT NULL,"
				+"userpassword TEXT NOT NULL,"
				+"integral int DEFAULT NULL,"
				+"falg int DEFAULT NULL,"
				+"first varchar(5) DEFAULT NULL," // 第一次：true  
				+"isCheck varchar(5) DEFAULT NULL," // 是否挂机  默认true
				+"email TEXT NOT NULL)"
				);
				
				//添加一条数据user
				// 加密密码：
				String password = MD5Util.convertMD5("admin");
				stat.executeUpdate("insert into user values(null,'1','"+password+"',10,0,'true','true','www.admin@qq.com')");

				//stat.executeUpdate("drop table if exists lexicon;");
				//创建表lexicon 词库表
				stat.executeUpdate("CREATE TABLE lexicon ("+
				 "id INTEGER PRIMARY KEY autoincrement NOT NULL,"+
				 "words varchar(50) DEFAULT NULL"+
				 ")");
				
				//stat.executeUpdate("drop table if exists tasklist;");
				//创建表tasklist 任务列表表
				stat.executeUpdate("CREATE TABLE tasklist ("+
				  "TaskId INTEGER PRIMARY KEY autoincrement  NOT NULL,"+
				  "Timer bigint DEFAULT NULL,"+
				  "ExecutingState varchar(50) DEFAULT NULL,"+
				  "Remarks varchar(50) DEFAULT NULL,"+
				  "SwitchingIP varchar(50) DEFAULT NULL,"+
				  "ArrivalMode varchar(50) DEFAULT NULL,"+
				  "YourWebsite varchar(50) DEFAULT NULL,"+
				  "TaskType int DEFAULT NULL,"+
				  "PV int DEFAULT NULL,"+
				  "PointAdsPage varchar(50) DEFAULT NULL,"+
				  "AdvertisingOptions varchar(50) DEFAULT NULL,"+
				  "PromotionClick varchar(50) DEFAULT NULL,"+
				  "SnapshotClick varchar(50) DEFAULT NULL,"+
				  "TimeOptions varchar(50) DEFAULT NULL,"+
				  "RollStopTime varchar(20) DEFAULT NULL,"+
				  "StopBeforeTime varchar(20) DEFAULT NULL,"+
				  "userId int DEFAULT NULL"+
				  ")");
				
				// stat.executeUpdate("drop table if exists website;");
				//创建表website 网址库表
				 stat.executeUpdate("CREATE TABLE website ("+
				  "id INTEGER PRIMARY KEY autoincrement NOT NULL,"+
				  "website varchar(250) DEFAULT NULL"+
				  ")");
				 
				//IP表
				 stat.executeUpdate("CREATE TABLE ip ("+
				  "id INTEGER PRIMARY  KEY autoincrement NOT NULL,"+
				  "outIp varchar(30) DEFAULT NULL,"+
				  "localIp varchar(30) DEFAULT NULL,"+
				  "date bigint(20) DEFAULT NULL"+
				  ")");
				 // 6.日志表
				stat.executeUpdate(
				"CREATE TABLE log (" +
				"id INTEGER PRIMARY KEY autoincrement NOT NULL," + 
				"name varchar(255) NOT NULL,"+ 
				"status INTEGER NOT NULL,"+  // 1：正常    2：成功      3：异常
				"date varchar(255) NOT NULL" + ")");
				
				//记录表
				 stat.executeUpdate("CREATE TABLE record ("+
				  "wordid INTEGER PRIMARY  KEY autoincrement NOT NULL,"+
				  "webUrl varchar(100) DEFAULT NULL,"+
				  "word varchar(100) DEFAULT NULL,"+
				  "time varchar(100) DEFAULT NULL,"+
				  "state INTEGER NOT NULL,"+ //   1表示成功  3表示失败
				  "taskId INTEGER NOT NULL,"+//关联任务表id
				  "userId INTEGER NOT NULL"+//登录用户的userID
				  ")");
				 
				 
			}catch(Exception e){
				e.printStackTrace();
			}
			/*ResultSet rs = stat.executeQuery("select * from user;");
			 while (rs.next()) {
			 System.out.println("id = " + rs.getInt("id"));
			}*/
			stat.close();
			
		} catch (Exception e) {
			System.out.println("数据库已存在");
		}finally{
			try {
				conn.close(); // 结束数据库的连接
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}
